/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.objectsearch.sqlsearch;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedList;

import javax.xml.parsers.ParserConfigurationException;

import org.objectsearch.connection.ConnectionInterface;
import org.objectsearch.parsers.ClassParserEngine;
import org.objectsearch.settings.LibSettings;
import org.xml.sax.SAXException;

import com.alianzamedica.controllers.constants.ActionConstants;

/**
 * @author Carlos
 *
 */
/**
 * @author Carlos
 *
 */
/**
 * it is the main class wich uses the search engine to generate que querys and
 * object lists by passing as parameter an object.
 * 
 * @author Carlos
 */
public class ObjectSearch {

	/**
	 * sets the current dialect for use in an oracle database
	 */
	public static final int ORACLE_DIALECT = 1;

	/**
	 * sets the current dialect for use in an mysql database
	 */
	public static final int MYSQL_DIALECT = 2;

	/**
	 * contains the default dialect used to create the paged query using oracle
	 * as default.
	 */
	private int defaultDialect = ORACLE_DIALECT;

	/**
	 * object batch wich is used to generate a store for objects used in a batch
	 * update.
	 */
	@SuppressWarnings("unchecked")
	private LinkedList objectBatch = new LinkedList();

	/**
	 * return the default dialect using the selected dialect.
	 * 
	 * @return the default dialect used to create a paged query
	 */
	public int getDefaultDialect() {
		return defaultDialect;
	}

	/**
	 * sets the default database for generating the paged querys
	 * 
	 * @param defaultDialect
	 *            is the selected dialect used from a list of dialects.
	 */
	public void setDefaultDialect(int defaultDialect) {
		this.defaultDialect = defaultDialect;
	}

	/**
	 * contains an arrayList of dataTypes wich is used to generate the query
	 * using comparators
	 */
	@SuppressWarnings("unchecked")
	private ArrayList list = new ArrayList();

	/**
	 * the engine that contains the instrucctions to build the parameters of the
	 * object search
	 */
	private ClassParserEngine engine;
	/**
	 * is the value wich determines if the search will use likes when making the
	 * string comparisions
	 */
	private boolean like = false;
	/**
	 * the connection interface that makes the connections to the database
	 */
	private ConnectionInterface connectionInterface;

	/**
	 * retrieves the value of the like in the search
	 * 
	 * @return the boolean wich determines if the search will use like in
	 *         comparing strings
	 */
	public boolean isLike() {
		return like;
	}

	/**
	 * sets the value of the like in the search
	 * 
	 * @param like
	 *            determines if the search will use like in comparing strings if
	 *            true the search will compare with a like comparator
	 */
	public void setLike(boolean like) {
		this.like = like;
	}

	/**
	 * constructor of the object search wich makes use of a string to locate the
	 * xml containing the mappings of the object
	 * 
	 * @param xmlSource
	 *            the xmlsource that contains the mappings of the database
	 * @param connectionInterface
	 *            is the main interface that contains the connections to the
	 *            database
	 * @throws javax.xml.parsers.ParserConfigurationException
	 *             if the parser can't be configured normally
	 * @throws org.xml.sax.SAXException
	 *             occurs if the document could not be read or is malformed
	 * @throws java.io.IOException
	 *             occurs if the document can't be accessed
	 */
	public ObjectSearch(String xmlSource,
			ConnectionInterface connectionInterface)
			throws ParserConfigurationException, SAXException, IOException {
		engine = new ClassParserEngine(xmlSource);
		this.connectionInterface = connectionInterface;
	}

	/**
	 * constructor of the object search wich makes use of a string to locate the
	 * xml containing the mappings of the object
	 * 
	 * @param xmlSource
	 *            the xmlsource that contains the mappings of the database
	 * @param connectionInterface
	 *            the classForname that contains the class for creating
	 *            connections
	 * @throws javax.xml.parsers.ParserConfigurationException
	 *             if the parser could not be configured
	 * @throws org.xml.sax.SAXException
	 *             if the document is malformed
	 * @throws java.io.IOException
	 *             if the document could not be read normally
	 * @throws ClassNotFoundException
	 *             if the class does not exist in the specified path.
	 * @throws InstantiationException
	 *             if the class could not be instantiated normally.
	 * @throws IllegalAccessException
	 *             if the class is read abnormally.
	 */
	@SuppressWarnings("unchecked")
	public ObjectSearch(String xmlSource, String connectionInterface)
			throws ParserConfigurationException, SAXException, IOException,
			ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		engine = new ClassParserEngine(xmlSource);
		Class _class = Class.forName(connectionInterface);
		this.connectionInterface = (ConnectionInterface) _class.newInstance();

	}

	/**
	 * constructor with an inputstream and a classforname for make the
	 * configuration of the search
	 * 
	 * @param in
	 *            contains the main parameter to create the object mappping
	 * @param connectionInterface
	 *            contains the interface wich connects to the database
	 * @throws javax.xml.parsers.ParserConfigurationException
	 *             if the parser can't be configured
	 * @throws SAXException
	 *             if the stream could not be parsed normally.
	 * @throws IOException
	 *             if the stream could not be read.
	 * @throws ClassNotFoundException
	 *             if the class does not exists.
	 * @throws InstantiationException
	 *             if the class could not be instantiated.
	 * @throws IllegalAccessException
	 *             if the class is read in an unusual way.
	 */
	@SuppressWarnings("unchecked")
	public ObjectSearch(InputStream in, String connectionInterface)
			throws ParserConfigurationException, SAXException, IOException,
			ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		engine = new ClassParserEngine(in);
		Class _class = Class.forName(connectionInterface);
		this.connectionInterface = (ConnectionInterface) _class.newInstance();
	}

	/**
	 * the constructor with a inputstream for reading the options and a
	 * connection interface to make the connections to the database.
	 * 
	 * @param in
	 *            is used to read the xml and generate the object mapping
	 * @param connectionInterface
	 *            the interface to interact with the database
	 * @throws javax.xml.parsers.ParserConfigurationException
	 *             if the parser can't be configured
	 * @throws org.xml.sax.SAXException
	 *             if the document could not be read normally
	 * @throws java.io.IOException
	 *             if the document can't be read
	 */
	public ObjectSearch(InputStream in, ConnectionInterface connectionInterface)
			throws ParserConfigurationException, SAXException, IOException {
		this.engine = new ClassParserEngine(in);
		this.connectionInterface = connectionInterface;
	}

	/**
	 * the constructor generated with a org.w3c.dom.Document interface and a
	 * connectionInterface to make the connections to the database.
	 * 
	 * @param document
	 *            the main document containing the mappings used to map the
	 *            objects to the database
	 * @param connectionInterface
	 *            is a class implementing the methods to connect to the
	 *            database.
	 */
	public ObjectSearch(org.w3c.dom.Document document,
			ConnectionInterface connectionInterface) {
		this.engine = new ClassParserEngine(document);
		this.connectionInterface = connectionInterface;
	}

	/**
	 * the main constructor using a org.w3c.dom.Document interface and a String
	 * to generate the classforname class containing the implementation for
	 * connecting to the database.
	 * 
	 * @param document
	 *            the main document containing the mappings used to map the
	 *            objects to the database
	 * @param connectionInterface
	 *            a String to create the class forname
	 * @throws ClassNotFoundException
	 *             if the implementing class is not found
	 * @throws InstantiationException
	 *             if the class can not be instantiated
	 * @throws IllegalAccessException
	 *             if the class can not be initiated normally
	 */
	@SuppressWarnings("unchecked")
	public ObjectSearch(org.w3c.dom.Document document,
			String connectionInterface) throws ClassNotFoundException,
			InstantiationException, IllegalAccessException {
		this.engine = new ClassParserEngine(document);
		Class _class = Class.forName(connectionInterface);
		this.connectionInterface = (ConnectionInterface) _class.newInstance();
	}

	/**
	 * adds a datatype to the object search
	 * 
	 * @param dataType
	 *            the datatype that contains the comparators to make the query
	 */
	@SuppressWarnings("unchecked")
	public void addDataType(DataType dataType) {
		list.add(dataType);
	}

	/**
	 * 
	 * @param fieldName
	 *            name of the field in the database
	 * @param comparator
	 *            the comparator used in the query
	 * @param value
	 *            value used to create the query in the database
	 */
	@SuppressWarnings("unchecked")
	public void addDataType(String fieldName, String comparator, Object value) {
		DataType dataType = new DataType(fieldName, comparator, value);
		list.add(dataType);
	}

	/**
	 * deletes all the datatypes used to make the query
	 */
	public void deleteAllDataTypes() {
		list.clear();
	}

	/**
	 * search the object using a default pagesize of 30 records
	 * 
	 * @param object
	 *            is the main object used to create the search in the main
	 *            database
	 * @return an arraylist containing the records in the database
	 * @throws Exception
	 *             if the arguments are not well formed
	 */
	@SuppressWarnings("unchecked")
	public ArrayList searchObjects(Object object) throws Exception {
		return this.searchObjects(object, null);
	}

	/**
	 * returns an arraylist of objects using a search in the database
	 * 
	 * @param object
	 *            the object used to make the query
	 * @param orders
	 *            the orders by used to sort the object
	 * @return an arraylist of objects using a query to the database
	 * @throws java.lang.Exception
	 *             if an error occurs
	 */
	@SuppressWarnings("unchecked")
	public ArrayList searchObjects(Object object, String[] orders)
			throws Exception {
		PreparedStatement psmt = null;
		ResultSet rs = null;
		ArrayList results = new ArrayList();
		try {
			this.connectionInterface.open();
			java.sql.Connection connection = this.connectionInterface
					.getConnection();
			SqlSearch search = new SqlSearch(connection, engine);
			search.setLike(this.like);
			search.setDataTypes(this.list);
			String sql = search.buildQuery(object, orders);
			psmt = connection.prepareStatement(sql);
			search.buildSearchWithParameters(psmt, object);
			if (LibSettings.DEBUG) {
				System.out.println(psmt.toString());
			}
			rs = psmt.executeQuery();
			results = search.getResults(rs, object);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (psmt != null) {
				try {
					psmt.close();
				} catch (Exception e) {
				}
			}
			try {
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
		return results;
	}

	/**
	 * insert an object to the database
	 * 
	 * @param object
	 *            the object to be inserted in the database
	 * @throws java.lang.Exception
	 *             if an error occurs
	 */
	public void insertObject(Object object) throws Exception {
		PreparedStatement buildInsertStatement = null;
		try {
			this.connectionInterface.open();
			java.sql.Connection connection = this.connectionInterface
					.getConnection();
			SqlSearch search = new SqlSearch(connection, engine);
			buildInsertStatement = search.buildInsertStatement(object);
			buildInsertStatement.execute();
		} finally {
			try {
				buildInsertStatement.close();
			} catch (Exception e) {
			}
			try {
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * deletes the object from the database
	 * 
	 * @param object
	 *            the object wich is going to be deleted from the database
	 * @throws java.lang.Exception
	 *             if error occurs
	 */
	public void deleteObject(Object object) throws Exception {
		PreparedStatement deleteStatement = null;
		try {
			this.connectionInterface.open();
			java.sql.Connection connection = this.connectionInterface
					.getConnection();
			SqlSearch search = new SqlSearch(connection, engine);
			deleteStatement = search.buildDeleteStatement(object);
			deleteStatement.execute();
		} finally {
			try {
				deleteStatement.close();
			} catch (Exception e) {
			}
			try {
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * updates the current object in the database.
	 * 
	 * @param object
	 *            the object wich is going to be updated.
	 * @throws Exception
	 *             if an exception occurs.
	 */
	public void updateObject(Object object) throws Exception {
		PreparedStatement buildUpdateStatement = null;
		try {
			this.connectionInterface.open();
			java.sql.Connection connection = this.connectionInterface
					.getConnection();
			SqlSearch search = new SqlSearch(connection, engine);
			buildUpdateStatement = search.buildUpdateStatement(object);
			buildUpdateStatement.execute();
		} finally {
			try {
				buildUpdateStatement.close();
			} catch (Exception e) {
			}
			try {
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * get the total pages in the database using a default number of records the
	 * default is 30
	 * 
	 * @param object
	 *            the object wich is used to search in the database
	 * @return the number of pages contained in the query
	 * @throws java.lang.Exception
	 *             if an error occurs
	 */
	public Integer getTotalPages(Object object) throws Exception {
		return this.getTotalPages(object, new Integer(ActionConstants.PAGE_SIZE));
	}

	/**
	 * the pages in total contained in the query engine
	 * 
	 * @param object
	 *            the object wich is used to search in the database
	 * @param numRegs
	 *            the number of records used in the database
	 * @return the number of pages contained in the query
	 * @throws java.lang.Exception
	 *             if an error occurs
	 */
	public Integer getTotalPages(Object object, Integer numRegs)
			throws Exception {
		Integer pages = new Integer(0);
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			this.connectionInterface.open();
			java.sql.Connection connection = this.connectionInterface
					.getConnection();
			SqlSearch search = new SqlSearch(connection, engine);
			search.setLike(this.like);
			search.setDataTypes(this.list);
			String sql = search.buildQuery(object, null);
			int index = sql.indexOf("from");
			if (index != -1) {
				sql = sql.substring(index);
				sql = " select  ceil(count(0)/" + numRegs + ") " + sql;
				if (LibSettings.DEBUG) {
					System.out.println(sql);
				}
				psmt = connection.prepareStatement(sql);
				search.buildSearchWithParameters(psmt, object);
				rs = psmt.executeQuery();
				while (rs.next()) {
					pages = new Integer(rs.getInt(1));
				}
			}
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (psmt != null) {
				try {
					psmt.close();
				} catch (Exception e) {
				}
			}
			try {
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
		return pages;
	}

	/**
	 * gets the arraylist of the objects made by the query in the database
	 * 
	 * @param object
	 *            the object that is requested to retrieve a list of objects
	 * @param ordersBy
	 *            the ordersBy that will set the order of the objects retrieved
	 *            in the database
	 * @param numPage
	 *            number of page wich is going to be retrieved in the query
	 * @param pageSize
	 *            number of regs by the page
	 * @return an arraylist of objects generated by the query
	 * @throws java.lang.Exception
	 *             occurs if error is thrown
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getPagedQuery(Object object, String[] ordersBy,
			Integer numPage, Integer pageSize) throws Exception {
		ArrayList arrayList = new ArrayList();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			this.connectionInterface.open();
			java.sql.Connection connection = this.connectionInterface
					.getConnection();
			SqlSearch search = new SqlSearch(connection, engine);
			search.setLike(this.like);
			search.setDataTypes(this.list);
			String sql = search.buildQuery(object, ordersBy);
			sql = this.buldPagedSql(sql, numPage, pageSize);
			psmt = connection.prepareStatement(sql);
			search.buildSearchWithParameters(psmt, object);
			if (LibSettings.DEBUG) {
				System.out.println(psmt.toString());
			}
			rs = psmt.executeQuery();
			arrayList = search.getResults(rs, object);
		} finally {
			if (psmt != null) {
				try {
					psmt.close();
				} catch (Exception ex) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			this.connectionInterface.close();
		}
		return arrayList;
	}

	/**
	 * constructs the sql containing the paged in oracle
	 * 
	 * @param sql
	 *            the sql containing the parameters not set to create a
	 *            preparedstatement
	 * @param pageNo
	 *            number of page in the query
	 * @param pageSize
	 *            the number of regs retrieved by the query made in the database
	 * @return the String containing the query ready for a paged search
	 */
	private String buldPagedSql(String sql, Integer pageNo, Integer pageSize) {
		String result = sql;
		switch (this.defaultDialect) {
		case ORACLE_DIALECT:
			return this.buldPagedSqlOracle(sql, pageNo, pageSize);
		case MYSQL_DIALECT:
			return this.buildPagedSQLMysql(sql, pageNo, pageSize);
		}
		return result;
	}

	/**
	 * constructs the sql containing the paged in oracle
	 * 
	 * @param sql
	 *            the sql containing the parameters not set to create a
	 *            preparedstatement
	 * @param pageNo
	 *            number of page in the query
	 * @param pageSize
	 *            the number of regs retrieved by the query made in the database
	 * @return the String containing the query ready for a paged search
	 */
	private String buldPagedSqlOracle(String sql, Integer pageNo,
			Integer pageSize) {
		int start = (pageNo.intValue() - 1) * pageSize.intValue() + 1;
		int end = start + pageSize.intValue() - 1;
		return "select *  " + "from ( select inner.*, rownum rn  "
				+ "		from 	(" + sql + ") inner " + "		where rownum <= " + end
				+ " ) " + "where rn >= " + start;
	}

	/**
	 * Constructs the pagedquery in mysql, with the current values.
	 * 
	 * @param sql
	 *            is the sql wich contains the parameters for the prepared
	 *            statements
	 * @param pageNo
	 *            is the number of page wich is used to bring the registers of
	 *            the paged query in mysql
	 * @param pageSize
	 *            is the size of the page wich is used to limit the number of
	 *            records in the query
	 * @return
	 */
	private String buildPagedSQLMysql(String sql, Integer pageNo,
			Integer pageSize) {
		int start = (pageNo.intValue() - 1) * pageSize.intValue();
		return sql + " limit " + start + " , " + pageSize.intValue() + " ";
	}

	/**
	 * sets the current library in debug mode wich enables the console output
	 * for the debugging information.
	 * 
	 * @param debug
	 *            contains the mode for library working, sets the System.out
	 *            enabled or disabled
	 */
	public void setDebug(boolean debug) {
		LibSettings.DEBUG = debug;
	}

	/**
	 * executes the batch that contains the objects in the linked list so you
	 * can see a batch update
	 * 
	 * @throws Exception
	 *             if the current batch could not be made while working with the
	 *             objects
	 */
	public void executeBatch() throws Exception {
		Connection connection = null;
		Exception exception = null;
		try {
			this.connectionInterface.open();
			connection = this.connectionInterface.getConnection();
			connection.setAutoCommit(false);
			SqlSearch search = new SqlSearch(connection, engine);
			while (!this.objectBatch.isEmpty()) {
				Object object = this.objectBatch.pop();
				PreparedStatement psmt = search.buildInsertStatement(object);
				if (LibSettings.DEBUG) {
					System.out.println("statement: " + psmt.toString());
				}
				psmt.execute();
			}
			connection.commit();
		} catch (Exception ex) {
			try {
				connection.rollback();
			} catch (Exception e) {
			}
			exception = ex;
		} finally {
			try {
				try {
					connection.setAutoCommit(true);
				} catch (Exception e) {
				}
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
		if (exception != null) {
			throw exception;
		}
	}

	/**
	 * ejecuta updates de manera masiva
	 * 
	 * @throws Exception
	 */
	public void executeBatchUpdate() throws Exception {
		Connection connection = null;
		Exception exception = null;
		try {
			this.connectionInterface.open();
			connection = this.connectionInterface.getConnection();
			connection.setAutoCommit(false);
			SqlSearch search = new SqlSearch(connection, engine);
			while (!this.objectBatch.isEmpty()) {
				Object object = this.objectBatch.pop();
				PreparedStatement psmt = search.buildUpdateStatement(object);
				if (LibSettings.DEBUG) {
					System.out.println("statement: " + psmt.toString());
				}
				psmt.execute();
			}
			connection.commit();
		} catch (Exception ex) {
			try {
				connection.rollback();
			} catch (Exception e) {
			}
			exception = ex;
		} finally {
			try {
				try {
					connection.setAutoCommit(true);
				} catch (Exception e) {
				}
				this.connectionInterface.close();
			} catch (Exception e) {
			}
		}
		if (exception != null) {
			throw exception;
		}
	}

	/**
	 * makes a custom query for retrieving objects from the specified class.
	 * 
	 * @param psmt
	 *            preparedstatement that contains the current query to the
	 *            database.
	 * @param con
	 *            the connection made to the database.
	 * @param _class
	 *            the class object wich is cointained in the arrayList
	 * @return an ArrayList containing the specified objects in the query made
	 *         to the database
	 * @throws Exception
	 *             if an exception occurs.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList customQuery(PreparedStatement psmt, Connection con,
			Class _class) throws Exception {
		ResultSet rs = null;
		ArrayList list = null;
		try {
			Object object = _class.newInstance();
			rs = psmt.executeQuery();
			SqlSearch sqlSearch = new SqlSearch(con, this.engine);
			list = sqlSearch.getResults(rs, object);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
		}
		return list;

	}

	// /**
	// * @param o1
	// * @param o2
	// * @param field
	// */
	// public void addBetween( Object o1 , Object o2 , String field ){
	// this.addDataType( field , "between" , o1);
	// this.addDataType( "" , "" , o2);
	// }

	/**
	 * sets the linked list to use a batchupdate in the objectsearch used
	 * settings
	 * 
	 * @param objectBatch
	 *            is the main queue used to store the objects to use later in a
	 *            batch
	 */
	@SuppressWarnings("unchecked")
	public void setObjectBatch(LinkedList objectBatch) {
		this.objectBatch = objectBatch;
	}

	/**
	 * gets the current batch used to create a batchupdate using the parameters
	 * of the database.
	 * 
	 * @return the current batch used to create a batchupdate using objects as
	 *         the arguments of the update
	 */
	@SuppressWarnings("unchecked")
	public LinkedList getObjectBatch() {
		return objectBatch;
	}

	/**
	 * adds an object to the current batch.
	 * 
	 * @param object
	 *            uses a mapped object to create a batch.
	 */
	@SuppressWarnings("unchecked")
	public void addBatchObject(Object object) {
		this.objectBatch.add(object);
	}

}
